package com.education.ai.dao.impl;

import com.education.ai.dao.MediaResourceDao;
import com.education.ai.entity.MediaResource;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 媒体资源数据访问实现类
 */
@Repository
public class MediaResourceDaoImpl implements MediaResourceDao {

    private static final Logger logger = LoggerFactory.getLogger(MediaResourceDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Override
    public List<MediaResource> findAll() {
        String sql = "SELECT * FROM media_resources";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql));
    }
    
    @Override
    public Optional<MediaResource> findById(Long id) {
        String sql = "SELECT * FROM media_resources WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToMediaResource(results.get(0)));
    }
    
    @Override
    public List<MediaResource> findBySubject(String subject) {
        String sql = "SELECT * FROM media_resources WHERE subject = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, subject));
    }
    
    @Override
    public List<MediaResource> findByGrade(String grade) {
        String sql = "SELECT * FROM media_resources WHERE grade = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, grade));
    }
    
    @Override
    public List<MediaResource> findByTopic(String topic) {
        String sql = "SELECT * FROM media_resources WHERE topic = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, topic));
    }
    
    @Override
    public List<MediaResource> findByResourceType(String resourceType) {
        String sql = "SELECT * FROM media_resources WHERE resource_type = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, resourceType));
    }
    
    @Override
    public List<MediaResource> findByTitleContaining(String keyword) {
        String sql = "SELECT * FROM media_resources WHERE title LIKE ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, "%" + keyword + "%"));
    }
    
    @Override
    public List<MediaResource> findByFormat(String format) {
        String sql = "SELECT * FROM media_resources WHERE format = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, format));
    }
    
    @Override
    public List<MediaResource> findByTeacherId(String teacherId) {
        String sql = "SELECT * FROM media_resources WHERE teacher_id = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, teacherId));
    }
    
    @Override
    public List<MediaResource> findBySubjectAndGrade(String subject, String grade) {
        String sql = "SELECT * FROM media_resources WHERE subject = ? AND grade = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, subject, grade));
    }
    
    @Override
    public List<MediaResource> findBySubjectAndGradeAndTopic(String subject, String grade, String topic) {
        String sql = "SELECT * FROM media_resources WHERE subject = ? AND grade = ? AND topic = ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, subject, grade, topic));
    }
    
    @Override
    public List<MediaResource> findWithPagination(int offset, int limit) {
        String sql = "SELECT * FROM media_resources LIMIT ? OFFSET ?";
        return mapResultsToMediaResources(jdbcUtil.executeQuery(sql, limit, offset));
    }
    
    @Override
    public MediaResource save(MediaResource mediaResource) {
        if (mediaResource.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO media_resources (subject, grade, topic, resource_type, title, teacher_id, " +
                    "description, url, duration, format, created_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            // 设置创建时间
            if (mediaResource.getCreatedTime() == null) {
                mediaResource.setCreatedTime(LocalDateTime.now());
            }
            
            long id = jdbcUtil.executeInsert(sql, 
                    mediaResource.getSubject(), 
                    mediaResource.getGrade(), 
                    mediaResource.getTopic(), 
                    mediaResource.getResourceType(), 
                    mediaResource.getTitle(), 
                    mediaResource.getTeacherId(),
                    mediaResource.getDescription(),
                    mediaResource.getUrl(),
                    mediaResource.getDuration(),
                    mediaResource.getFormat(),
                    Timestamp.valueOf(mediaResource.getCreatedTime()));
            
            mediaResource.setId(id);
            return mediaResource;
        } else {
            // 更新操作
            update(mediaResource);
            return mediaResource;
        }
    }
    
    @Override
    public boolean update(MediaResource mediaResource) {
        String sql = "UPDATE media_resources SET subject = ?, grade = ?, topic = ?, resource_type = ?, " +
                "title = ?, teacher_id = ?, description = ?, url = ?, duration = ?, format = ? WHERE id = ?";
        
        int rows = jdbcUtil.executeUpdate(sql,
                mediaResource.getSubject(),
                mediaResource.getGrade(),
                mediaResource.getTopic(),
                mediaResource.getResourceType(),
                mediaResource.getTitle(),
                mediaResource.getTeacherId(),
                mediaResource.getDescription(),
                mediaResource.getUrl(),
                mediaResource.getDuration(),
                mediaResource.getFormat(),
                mediaResource.getId());
        
        return rows > 0;
    }
    
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM media_resources WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM media_resources";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    @Override
    public long countBySubject(String subject) {
        String sql = "SELECT COUNT(*) AS count FROM media_resources WHERE subject = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, subject);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    @Override
    public long countByResourceType(String resourceType) {
        String sql = "SELECT COUNT(*) AS count FROM media_resources WHERE resource_type = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, resourceType);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为媒体资源对象列表
     */
    private List<MediaResource> mapResultsToMediaResources(List<Map<String, Object>> results) {
        List<MediaResource> mediaResources = new ArrayList<>();
        for (Map<String, Object> row : results) {
            mediaResources.add(mapRowToMediaResource(row));
        }
        return mediaResources;
    }
    
    /**
     * 将单行结果映射为媒体资源对象
     */
    private MediaResource mapRowToMediaResource(Map<String, Object> row) {
        MediaResource mediaResource = new MediaResource();
        mediaResource.setId(((Number) row.get("id")).longValue());
        mediaResource.setSubject((String) row.get("subject"));
        mediaResource.setGrade((String) row.get("grade"));
        mediaResource.setTopic((String) row.get("topic"));
        mediaResource.setResourceType((String) row.get("resource_type"));
        mediaResource.setTitle((String) row.get("title"));
        mediaResource.setTeacherId((String) row.get("teacher_id"));
        mediaResource.setDescription((String) row.get("description"));
        mediaResource.setUrl((String) row.get("url"));
        
        if (row.get("duration") != null) {
            mediaResource.setDuration(((Number) row.get("duration")).intValue());
        }
        
        mediaResource.setFormat((String) row.get("format"));
        
        // 处理日期时间类型
        if (row.get("created_time") != null) {
            Object timeObj = row.get("created_time");
            if (timeObj instanceof java.sql.Timestamp) {
                mediaResource.setCreatedTime(((java.sql.Timestamp) timeObj).toLocalDateTime());
            } else if (timeObj instanceof LocalDateTime) {
                mediaResource.setCreatedTime((LocalDateTime) timeObj);
            } else if (timeObj instanceof java.util.Date) {
                // 将java.util.Date转换为LocalDateTime
                java.util.Date date = (java.util.Date) timeObj;
                mediaResource.setCreatedTime(new java.sql.Timestamp(date.getTime()).toLocalDateTime());
            } else {
                logger.warn("无法识别的创建时间类型: {}, 值: {}", timeObj.getClass().getName(), timeObj);
            }
        }
        
        return mediaResource;
    }
} 